package ocg.common.re.read;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

/**
 * Created by chenbo on 2017/12/7.
 */
public class ExcelTable extends ExcelPOI{

    Logger logger = LogManager.getLogger ( this.getClass () );

    Sheet sheet;

    public void setSheet(Sheet sheet) {
        this.sheet = sheet;
    }

    /**
     * 根据关键字反推单元格坐标
     * @param CellValue
     * @return
     */
    public String getIndex(String CellValue){
        int y = 0;
        int x = 0;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        for ( int i = 0 ; i< rowEnd ; i++ ){
            Row row = sheet.getRow ( i );
            int cellEnd = row.getPhysicalNumberOfCells ();
            for ( int j = 0  ; j < cellEnd ; j++ ){
                Cell cell = row.getCell ( j );
                String value = stringValue ( cell );
                if ( value != null) {
                    if (value.equals ( CellValue )) {
                        y = i;
                        x = j;
                    }
                }
            }
        }
        Index ind = new Index ();
        return ind.get ( x , y );
    }

    /**
     * 根据单元格坐标，获取单元格的值
     * @param index
     * @return
     */
    public String getCell( String index ){
        Index ind = new Index ();
        ind.set ( index );
        Row row = sheet.getRow ( ind.y-1 );
        Cell cell = row.getCell ( ind.x );
        return stringValue ( cell );
    }

    /**
     * excel 坐标变换
     *
     */
    class Index{

        public int x;
        public int y;

        /**
         * (x,y) <=> A1
         * @param x
         * @param y
         * @return
         */
        public String get( int x , int y ){
            this.x = x;
            this.y = y;
            String index = new StringBuffer ( ).append ( (char) (x+65) ).append ( y+1 ).toString ();
            return index;
        }

        public void set( String index){

            if ( index.length () == 2 ){
                char cell_1 = index.charAt ( 0 );
                if ( Character.isUpperCase ( cell_1 )){
                    x = cell_1-65;
                    char cell_2 = index.charAt ( 1 );
                    if ( Character.isDigit ( cell_2 )){
                        if ( cell_2-48 > 0) {
                            y = cell_2 - 48;
                        }else {
                            logger.error ( "不符合限定规则，必须是数字大于0：" + index );
                        }
                    }else {
                        logger.error ( "不符合限定规则，必须是数字结束：" + index );
                    }
                }else {
                    logger.error ( "不符合限定规则，必须是大写开头：" + index );
                }

            } else {
                logger.error ( "不符合限定规则，必须是两个字符：" + index );
            }
        }

        public void set( int x , int y ){
            this.x = x;
            this.y = y;
        }
    }
}
